package com.css.fxfzcpxx.modules.certificateManage.repository.impl;

import com.css.fxfzcpxx.constants.YNEnum;
import com.css.fxfzcpxx.modules.certificateManage.entity.CertificateManage;
import com.css.fxfzcpxx.modules.certificateManage.entity.CertificateRule;
import com.css.fxfzcpxx.modules.certificateManage.entity.UserCertificate;
import com.css.fxfzcpxx.modules.certificateManage.repository.CertificateManageRepository;
import com.css.fxfzcpxx.modules.certificateManage.vo.CertificateRuleVo;
import com.css.fxfzcpxx.modules.courseManage.entity.CourseManage;
import com.css.fxfzcpxx.modules.questionData.entity.QuestionData;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

@Repository
public class CertificateManageRepositoryImpl implements CertificateManageRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "cpxx_certificate_manage";

    @Override
    public void save(CertificateManage certificateManage) {
        String sql = "insert into "+TABLE_NAME+"(id,certificate_name,picture_name,create_user,create_time,del_flag) " +
                "values(:id,:certificateName,:pictureName,:createUser, :createTime, :delFlag)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(certificateManage);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void update(CertificateManage certificateManage) {
        String sql = "update "+TABLE_NAME+" set certificate_name=:certificateName,certificate_content = :certificateContent," +
                "certificate_file = :certificateFile ,certificate_level=:CertificateLevel," +
                "update_user = :updateUser ,update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(certificateManage);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void previewDel(String id) {
        String sql = "update "+TABLE_NAME+" set del_flag = '"+ YNEnum.Y.toString()+"' where id = '" + id + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<CertificateManage> queryAll() {
        String sql="select * from "+TABLE_NAME+" where del_flag = '"+ YNEnum.N.toString()+"' order by create_time desc";
        List<CertificateManage> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CertificateManage.class));
        return list;
    }

    @Override
    public void updateRule(CertificateRule rule) {
        String sql = "update cpxx_certificate_rule set certificate_id =:certificateId , greater_than= :greaterThan, less_than=:lessThan where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(rule);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public CertificateManage queryByName(String pictureName) {
        String sql ="select * from "+TABLE_NAME+" where del_flag = '"+ YNEnum.N.toString()+"' and picture_name = '"+pictureName+"'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CertificateManage.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //查询规则
    @Override
    public List<CertificateRuleVo> queryRule() {
        String sql="select a.*,b.certificate_name from cpxx_certificate_rule a left join cpxx_certificate_manage b " +
                "on a.certificate_id=b.id where b.del_flag='0' order by a.sign";
        List<CertificateRuleVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CertificateRuleVo.class));
        return list;
    }

    @Override
    public void saveRule(CertificateRule rule) {
        String sql = "insert into cpxx_certificate_rule (id,certificate_id,greater_than,less_than,sign) " +
                "values(:id,:certificateId,:greaterThan,:lessThan,:sign)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(rule);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public CertificateRule findRuleById(String id) {
        String sql ="select * from cpxx_certificate_rule where  id = '"+id+"'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CertificateRule.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //删除证书颁发规则
    @Override
    public void deleteRule() {
        String sql = "delete from cpxx_certificate_rule";
        jdbcTemplate.update(sql);
    }

    @Override
    public void saveUserCertificate(UserCertificate userCertificate) {
        String sql = "insert into cpxx_user_certificate (id,course_id,test_id,template_id,certificate_id,create_user,create_time) " +
                "values(:id,:courseId,:testId, :templateId, :certificateId,:createUser,:createTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(userCertificate);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public CertificateManage findById(String id) {
        String sql ="select * from "+TABLE_NAME+" where del_flag = '"+ YNEnum.N.toString()+"' and id = '"+id+"'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CertificateManage.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<UserCertificate> getUserCertificate(String userID) {
        String sql="select * from cpxx_user_certificate where create_user = '"+ userID+"'";
        List<UserCertificate> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserCertificate.class));
        return list;
    }

    @Override
    public CertificateRule findRuleByCertificateId(String id) {
        String sql ="select * from cpxx_certificate_rule where  certificate_id = '"+id+"'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CertificateRule.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
}
